﻿CREATE Procedure state_MailChildStatus
	@ChildID int,
	@StatusFromID int = 0,
	@StatusToID int = 0
As

DECLARE @Subject varchar(512), @Text nvarchar(4000), @To nvarchar(1024),@TableName sysname,@StatusName sysname,@StatusParent sysname
SELECT @To = Value FROM fs_StatesParamValue_MailRecipients() WHERE StateID = @StatusToID
IF ISNULL(@To,'') = '' RETURN


SELECT @TableName = S.TableName,@StatusName = S.NameLong,@StatusParent = ParentName FROM v_State S WHERE S.ID = @StatusToID

DECLARE @LoanID sysname
DECLARE @SQL nvarchar(max)
SET @SQL = N'SELECT @LoanID = LoanID FROM '+@TableName+' WHERE ID = @ChildID'
EXEC sp_ExecuteSQL @SQL,N'@ChildID int,@LoanID sysname OUT',@ChildID,@LoanID OUT

DECLARE @LoanAddress sysname SET @LoanAddress = dbo.Registry_ShowLoanServer()+dbo.Registry_ShowLoanUrl()+@LoanID
DECLARE @LoanAddressLocal sysname SET @LoanAddressLocal = dbo.Registry_ShowLoanServerLocal()+dbo.Registry_ShowLoanUrl()+@LoanID

--SELECT @To [To],@TableName TableName,@StatusParent StatusParent,@LoanID LoanID,@StatusName StatusName,@LoanAddress LoanAddress,@LoanAddressLocal LoanAddressLocal
DECLARE @ObjectName sysname SET @ObjectName = dbo.clr_RegEx_Replace(@TableName,'^[^_]+_','',1,0,1)
PRINT 'New '+@ObjectName+' with status ['+ @StatusName+'] has arrived to LoanID:'+@LoanID+'
'+@LoanAddress+'
'+@LoanAddressLocal+'
'
SET @Subject = 'New '+@ObjectName+' with status ['+ @StatusName+'] has arrived to LoanID:'+@LoanID
SET @Text = @LoanAddress+'
'+@LoanAddressLocal
EXEC admin.s_Mail_System @Subject,@Text,'',@To

